package fu.capstone.hanoicityguide.dataaccess.nightlife;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import fu.capstone.hanoicityguide.R;
import fu.capstone.hanoicityguide.business.model.Category;
import fu.capstone.hanoicityguide.business.model.GeoCoordinate;
import fu.capstone.hanoicityguide.business.model.NightLife;
import fu.capstone.hanoicityguide.dataaccess.base.BaseDAOImpl;
import fu.capstone.hanoicityguide.dataaccess.base.DatabaseHelper;

public class NightLifeDAOImp extends BaseDAOImpl implements NightLifeDAO {

	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	private Context context;

	public NightLifeDAOImp(Context ctx) {
		super(ctx);
		this.context = ctx;
	}

	@Override
	public ArrayList<NightLife> listNightLife(ArrayList<String> filter,
			String sorter) {
		Cursor cursor = null;
		ArrayList<NightLife> arrResult = new ArrayList<NightLife>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryNightLife = "SELECT Place.[Id] , Place.[Name] , Geolocation.[Latitude], Geolocation.[Longitude], "
					+ "Category.[CategoryName] , Type.[TypeName] , Rating.[RatingValue] , Place.[Price] ,"
					+ " AlbumImage.[IconPath] FROM Place "
					+ "LEFT JOIN Geolocation ON Place.[GeoId] = Geolocation.[Id] "
					+ "LEFT JOIN Category ON Place.[CategoryId] = Category.[Id] "
					+ "LEFT JOIN Type ON Place.[TypeId] = Type.[Id] "
					+ "LEFT JOIN Rating ON Place.[RatingId] = Rating.[Id] "
					+ "LEFT JOIN AlbumImage ON Place.[AlbumImageId] = AlbumImage.[Id] "
					+ "WHERE Category.[CategoryName] == \"Nightlife\" ";

			// filter
			if (filter.size() > 0) {
				queryNightLife = queryNightLife
						+ " AND ( Type.[TypeName] == \"" + filter.get(0) + "\"";
				for (int i = 1; i < filter.size(); i++) {
					queryNightLife = queryNightLife
							+ " OR Type.[TypeName] == \"" + filter.get(i)
							+ "\"";
				}
				queryNightLife = queryNightLife + " ) ";
			} else {
				queryNightLife = queryNightLife
						+ " AND Type.[TypeName] == \"\"";
			}

			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryNightLife = queryNightLife
						+ "ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryNightLife = queryNightLife
						+ "ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryNightLife = queryNightLife + "ORDER BY Place.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryNightLife = queryNightLife + "ORDER BY Place.[Name] ASC";
			} else {
			}
			cursor = database.rawQuery(queryNightLife, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {

						NightLife nightlife = new NightLife();
						nightlife.setId(cursor.getInt(cursor
								.getColumnIndex("Id")));
						nightlife.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						nightlife.setQuality(cursor.getInt(cursor
								.getColumnIndex("RatingValue")));
						nightlife.setCategory(cursor.getString(cursor
								.getColumnIndex("CategoryName")));
						nightlife.setType(cursor.getString(cursor
								.getColumnIndex("TypeName")));

						GeoCoordinate geo = new GeoCoordinate();
						geo.setLatitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Latitude"))));
						geo.setLongitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Longitude"))));
						nightlife.setGeo(geo);

						nightlife.setIconImage(cursor.getString(cursor
								.getColumnIndex("IconPath")));

						arrResult.add(nightlife);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;
	}

	@Override
	public NightLife browseNightLife(int nightlifeID) {
		Cursor cursor = null;
		NightLife nightlife = new NightLife();
		ArrayList<String> arrImage = new ArrayList<String>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryNightLife = "SELECT Place.[Id] , Place.[Name] , Geolocation.[Longitude] , Geolocation.[Latitude] , "
					+ "Category.[CategoryName] , Type.[TypeName] , Rating.[RatingValue] , Place.[Price] , Place.[Description] , "
					+ "Place.[OpenTime] , Place.[CloseTime] , Place.[Address] , Place.[PhoneNumber] , Place.[Website] , AlbumImage.[ImagePath1] , "
					+ "AlbumImage.[ImagePath2] , AlbumImage.[ImagePath3] , AlbumImage.[ImagePath4] , AlbumImage.[ImagePath5] FROM Place "
					+ "LEFT JOIN Geolocation ON Place.[GeoId] = Geolocation.[Id] "
					+ "LEFT JOIN Category ON Place.[CategoryId] = Category.[Id] "
					+ "LEFT JOIN AlbumImage ON Place.[AlbumImageId] = AlbumImage.[Id] "
					+ "LEFT JOIN Type ON Place.[TypeId] = Type.[Id] "
					+ "LEFT JOIN Rating ON Place.[RatingId] = Rating.[Id] "
					+ "WHERE Category.[CategoryName] == \"Nightlife\" "
					+ "AND Place.[Id] == " + nightlifeID;
			cursor = database.rawQuery(queryNightLife, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						nightlife.setId(cursor.getInt(cursor
								.getColumnIndex("Id")));
						nightlife.setType(cursor.getString(cursor
								.getColumnIndex("TypeName")));
						nightlife.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						nightlife.setDescription(cursor.getString(cursor
								.getColumnIndex("Description")));
						nightlife.setAddress(cursor.getString(cursor
								.getColumnIndex("Address")));
						nightlife.setPhoneNumber(cursor.getString(cursor
								.getColumnIndex("PhoneNumber")));
						nightlife.setWebsite(cursor.getString(cursor
								.getColumnIndex("Website")));
						nightlife.setOpenTime(cursor.getString(cursor
								.getColumnIndex("OpenTime")));
						nightlife.setCloseTime(cursor.getString(cursor
								.getColumnIndex("CloseTime")));

						GeoCoordinate geo = new GeoCoordinate();
						geo.setLatitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Latitude"))));
						geo.setLongitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Longitude"))));
						nightlife.setGeo(geo);

						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath1")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath2")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath3")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath4")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath5")));

						nightlife.setArrImage(arrImage);
					} while (cursor.moveToNext());

				}
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			closeDB(database, dbHelper);
		}
		return nightlife;
	}

	@Override
	public String[] getListType(int categoryID) {
		// TODO Auto-generated method stub
		return super.getListType(categoryID);
	}

	@Override
	public ArrayList<Category> getListCategory(String filter) {
		// TODO Auto-generated method stub
		return super.getListCategory(filter);
	}

	@Override
	public ArrayList<NightLife> searchNightLife(String keyWord,
			ArrayList<String> filter, String sorter) {
		// Toast.makeText(context, "Call searchAttraction",
		// Toast.LENGTH_LONG).show();
		Cursor cursor = null;
		ArrayList<NightLife> arrResult = new ArrayList<NightLife>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryNightLife = "SELECT Place.[Id] , Place.[Name] , "
					+ "Category.[CategoryName] , Type.[TypeName] , Rating.[RatingValue] , Place.[Price] ,"
					+ " AlbumImage.[IconPath] FROM Place "
					+ "LEFT JOIN Geolocation ON Place.[GeoId] = Geolocation.[Id] "
					+ "LEFT JOIN Category ON Place.[CategoryId] = Category.[Id] "
					+ "LEFT JOIN Type ON Place.[TypeId] = Type.[Id] "
					+ "LEFT JOIN Rating ON Place.[RatingId] = Rating.[Id] "
					+ "LEFT JOIN AlbumImage ON Place.[AlbumImageId] = AlbumImage.[Id] "
					+ "WHERE Category.[CategoryName] == \"Nightlife\" ";

			// filter
			if (filter.size() > 0) {
				queryNightLife = queryNightLife
						+ " AND ( Type.[TypeName] == \"" + filter.get(0) + "\"";
				for (int i = 1; i < filter.size(); i++) {
					queryNightLife = queryNightLife
							+ " OR Type.[TypeName] == \"" + filter.get(i)
							+ "\"";
				}
				queryNightLife = queryNightLife + " ) ";
			} else {
				queryNightLife = queryNightLife
						+ " AND Type.[TypeName] == \"\"";
			}

			// filter by keyword
			queryNightLife = queryNightLife + " AND Place.[Name] LIKE '%"
					+ keyWord + "%' ";

			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryNightLife = queryNightLife
						+ "ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryNightLife = queryNightLife
						+ "ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryNightLife = queryNightLife + "ORDER BY Place.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryNightLife = queryNightLife + "ORDER BY Place.[Name] ASC";
			} else {
			}

			// execute query
			cursor = database.rawQuery(queryNightLife, null);

			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						NightLife nightlife = new NightLife();
						nightlife.setId(cursor.getInt(cursor
								.getColumnIndex("Id")));
						nightlife.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						nightlife.setQuality(cursor.getInt(cursor
								.getColumnIndex("RatingValue")));
						nightlife.setCategory(cursor.getString(cursor
								.getColumnIndex("CategoryName")));
						nightlife.setType(cursor.getString(cursor
								.getColumnIndex("TypeName")));
						nightlife.setIconImage(cursor.getString(cursor
								.getColumnIndex("IconPath")));

						arrResult.add(nightlife);
					} while (cursor.moveToNext());
				}

			}
		} catch (Exception e) {
			Log.v("Can't query DB", e.toString());
		} finally {

			closeDB(database, dbHelper);
		}
		return arrResult;
	}

}
